Create database HealthInsuranceMgmt
go
use HealthInsuranceMgmt
go
insert into AdminLogin values('admin','yDc1y4fjKLI=')  

/* Create table Admin Login*/
create table AdminLogin(
	username varchar(50) primary key,
	[Password] varchar(50) not null
)


go
/* Create table Company Detail*/
create table CompanyDetails(
	CompanyId int identity primary key,
	CompanyName varchar(50) not null,
	[Address] varchar(150),
	Phone varchar(20),
	CompanyUrl varchar(50)
)

go
/* create table Employee Register*/

create  table EmpRegister(
	EmpNo int Identity primary key,
	Designation varchar(50),
	Joindate datetime,
	Salary money,
	FirstName varchar(50),
	LastName varchar(50),
	UserName varchar(50),
	[PassWord] varchar(50),
	[Address] varchar(150),
	ContactNo varchar(50),
	[State] varchar(50),
	Country varchar(50),
	City varchar(50),
	PolicyStatus varchar(30),
	PolicyId int
	
	)

/* Create table HospitalInfo*/
go
Create table HospitalInfo(
	HospitalId varchar(50) primary key,
	HospitalName varchar(50),
	PhoneNO varchar(50),
	Location varchar(50),
	Url varchar(50),
	
)
go
/* create table Policies*/
create table Policies(
	
PolicyId int identity primary key,
PolicyName varchar(50),
PolicyDesc varchar(150),
Amount money,
Emi money,
CompanyId int,
MedicalId varchar(50)
)
go 
/* create table Policies on Employee */

create table policiesonemployees
(
	EmpNo varchar(10) not null,
	PolicyId int not null,
	PolicyName varchar(50) not null,
	policyAmount money not null,
	PolicyDuration float not null,
	Emi float not null,
	PStartDate datetime not null,
	PendDate datetime not null,
	CompanyId varchar(30) not null,
	CompanyName varchar(50) not null,
	Medical varchar(50) not null
)

go
/* create table Policy Approval Details*/

create  table 	PolicyApprovalDetails(
	PolicyId int primary key,
	RequestId int,
	[Date] datetime,
	Amount money,
	[Status] char(3),
	Reason varchar(50)
)

go
/* Create table  Policy Request Details*/
create  table	PolicyRequestDetails(
	RequestId int primary key identity,
	RequestDate datetime,
	EmpNo int not null,
	PolicyId int,
	PolicyName varchar(50),
	PolicyAmount money,
	Emi money,
	CompanyId int,
	CompanyName varchar(50),
	[Status] varchar(50) not null,
)
go
/* create table Policy Total Description*/

create table 	PolicyTotalDescription(
	PolicyId int not null,
	PolicyName varchar(50),
	PolicyDes varchar(250),
	PolicyAmount money,
	Emi money,
	PolicyDurationMonths int,
	CompanyName varchar(50) not null,
	MedicalId varchar(50) not null
	
	
)

Go
Create  table News(
id int primary key identity,
title varchar(100),
[description] varchar(max)
)
/* alter table EmpRegister add constraint FK_EmpRegister foreign key(PolicyId) references Policies(PolicyId)*/
alter table PolicyTotalDescription add constraint FK_PoTotDes foreign key(PolicyId) references Policies(PolicyId)
alter table policiesonemployees add constraint FK_Poemp foreign key(PolicyId) references Policies(PolicyId) on update cascade on delete cascade
alter table PolicyRequestDetails add constraint FK_Poreqdet foreign key(PolicyId) references Policies(PolicyId)

/*alter table EmpRegister add constraint FK_EmpRegister2 foreign key(PolicyId) references PolicyApprovalDetails(PolicyId)*/
alter table PolicyTotalDescription add constraint FK_PoTotDes2 foreign key(PolicyId) references PolicyApprovalDetails(PolicyId)
/* alter table policiesonemployees add constraint FK_Poemp2 foreign key(PolicyId) references PolicyApprovalDetails(PolicyId)*/
/*alter table PolicyRequestDetails drop constraint FK_Poreqdet2 foreign key(PolicyId) references PolicyApprovalDetails(PolicyId)*/




alter table PolicyRequestDetails  add constraint FK_CompDet foreign key(CompanyId) references CompanyDetails(CompanyId)
alter table Policies  add constraint FK_Policies foreign key(CompanyId) references CompanyDetails(CompanyId)
alter table PolicyRequestDetails  add constraint FK_Poreqde foreign key(EmpNo) references EmpRegister(EmpNo)

go
insert into CompanyDetails values('Company A','Cau Giay-Ha Noi','041785950','CompanyA.com.vn')
insert into CompanyDetails values('Company B','Hoang Mai-Ha Noi','046573453','CompanyB.com.vn')
insert into CompanyDetails values('Company C','Van Dinh-Ha Noi','042345453','CompanyC.com.vn')
insert into CompanyDetails values('Company D','Co Nhue-Ha Noi','045673457','CompanyD.com.vn')
insert into CompanyDetails values('Company E','Ha Dong-Ha Noi','042345632','CompanyE.com.vn')
insert into CompanyDetails values('Company F','Chuong My-Ha Noi','041253435','CompanyF.com.vn')
insert into CompanyDetails values('Company G','Cau Giay-Ha Noi','041785950','CompanyG.com.vn')


/*List Proceduce*/
go
CREATE PROC CheckAdminLogin
(
@UserName varchar(50),
@PassWord varchar(50)
)
AS
BEGIN
	SELECT * FROM AdminLogin WHERE username=@UserName and [Password]=@PassWord
END
GO
CREATE PROC ChangePassAdmin(
@UserName varchar(50),
@PassWord varchar(50)
)AS
BEGIN
	UPDATE AdminLogin SET [Password]=@PassWord WHERE username=@UserName
END
Go
CREATE  PROC ListCompany
AS
SELECT * FROM CompanyDetails

GO

CREATE PROC InsertCompany(
@CompanyName varchar(50),
@CompanyAddress varchar(150),
@CompanyPhone varchar(20),
@CompanyUrl varchar(50)
)
AS
INSERT INTO CompanyDetails VALUES(@CompanyName,@CompanyAddress,@CompanyPhone,@CompanyUrl)

GO

CREATE PROC DeleteCompany(
@CompanyID int
)
AS
DELETE CompanyDetails WHERE CompanyId=@CompanyID

GO
CREATE  PROC UpdateCompany(
@CompanyID int,
@CompanyName varchar(50),
@CompanyAddress varchar(150),
@CompanyPhone varchar(20),
@CompanyUrl varchar(50)
)
AS
UPDATE CompanyDetails SET CompanyName=@CompanyName,[Address]=@CompanyAddress,Phone=@CompanyPhone,CompanyUrl=@CompanyUrl where CompanyId = @CompanyID

GO
CREATE PROC GetCompanyById(
@CompanyID varchar(50)
)
AS
SELECT * FROM CompanyDetails WHERE CompanyId=@CompanyID


GO 
CREATE PROC listemployee
AS
SELECT * FROM EmpRegister


GO
CREATE  PROC InsertEmployee(
@Designation varchar(50),
@Joindate datetime,
@Salary money,
@FirstName varchar(50),
@LastName varchar(50),
@UserName varchar(50),
@PassWord varchar(50),
@Address varchar(150),
@ContactNo varchar(50),
@State varchar(50),
@Country varchar(50),
@City varchar(50),
@PolicySatus varchar(30),
@PolicyId	int
)
AS
INSERT INTO EmpRegister VALUES(@Designation,@Joindate ,@Salary ,@FirstName, @LastName ,@UserName, @PassWord ,@Address ,@ContactNo, @State ,@Country ,@City, @PolicySatus, @PolicyId)

GO 
CREATE PROC UpdateInFoEmp(
@EmpNo int,
@Designation varchar(50),
@Joindate datetime,
@Salary money,
@FirstName varchar(50),
@LastName varchar(50),
@UserName varchar(50),
@PassWord varchar(50),
@Address varchar(150),
@ContactNo varchar(50),
@State varchar(50),
@Country varchar(50),
@City varchar(50)

)
AS
UPDATE EmpRegister SET Designation=@Designation,Joindate=@Joindate,Salary=@Salary,FirstName=@FirstName,LastName=@LastName,[Address]=@Address,ContactNo=@ContactNo,[State]=@State,Country=@Country,City=@City WHERE EmpNo=@EmpNo
GO
CREATE PROC DeleteEmployee
(@EmpNo int
)AS DELETE EmpRegister WHERE EmpNo=@EmpNo

GO
CREATE PROC GetEmpById(
@EmpNo int
)AS SELECT * FROM EmpRegister WHERE EmpNo=@EmpNo

GO 

CREATE PROC ListHospital
AS
SELECT * FROM HospitalInfo

GO 

CREATE PROC InsertHospital(
@HospitalId varchar(50),
@HospitalName varchar(50),
@PhoneNO varchar(50),
@Location varchar(50),
@Url varchar(50)
)
AS
INSERT INTO HospitalInfo VALUES(@HospitalId,@HospitalName,@PhoneNO,@Location,@Url)

GO

CREATE PROC DeleteHospital(
@HospitalId int
)
AS
DELETE HospitalInfo WHERE HospitalId=@HospitalId

GO
CREATE PROC UpdateHospitalInfo(
@HospitalId int,
@HospitalName varchar(50),
@PhoneNO varchar(50),
@Location varchar(50),
@Url varchar(50)
)
AS
BEGIN
	UPDATE HospitalInfo SET HospitalName=@HospitalName,PhoneNO=@PhoneNO,Location=@Location,Url=@Url WHERE HospitalId=@HospitalId
END

GO
CREATE PROC GetHospitalById(
	@HospitalId int
)
AS
SELECT * FROM HospitalInfo WHERE HospitalId=@HospitalId

Go
CREATE PROC ListPolicy
AS
SELECT * FROM Policies

Go

CREATE PROC InsertPolicy
(

@PolicyName varchar(50),
@PolicyDesc varchar(150),
@Amount money,
@Emi money,
@CompanyId int,
@MedicalId	 varchar(50)
)
AS
INSERT INTO Policies VALUES(@PolicyName,@PolicyDesc,@Amount,@Emi,@CompanyId,@MedicalId)
GO

CREATE PROC DeletePolicy(
@PolicyId int
)
AS
DELETE Policies WHERE PolicyId=@PolicyId

GO
CREATE  PROC UpdatePolicyInfo (
@PolicyId int,
@PolicyName varchar(50),
@PolicyDesc varchar(150),
@Amount money,
@Emi money,
@CompanyId int,
@MedicalId	 varchar(50)
)
AS
UPDATE Policies SET PolicyName=@PolicyName,PolicyDesc=@PolicyDesc,Amount=@Amount,Emi=@Emi,CompanyId=@CompanyId,MedicalId=@MedicalId WHERE PolicyId=@PolicyId

GO

CREATE PROC GetpolicyById(
@PolicyId int
)
AS
SELECT * FROM Policies WHERE PolicyId=@PolicyId

GO

CREATE PROC listPoEmp
AS
SELECT * FROM Policies

Go
CREATE PROC InsertPoEmp(
@EmpNo varchar(10),
@PolicyId int,
@PolicyName varchar(50),
@policyAmount money,
@PolicyDuration float,
@Emi float,
@PStartDate datetime,
@PendDate datetime,
@CompanyId varchar(30),
@CompanyName varchar(50),
@Medical varchar(50)

)
AS
INSERT INTO policiesonemployees VALUES(@EmpNo,@PolicyId,@PolicyName,@policyAmount,@PolicyDuration,@Emi,@PStartDate,@PendDate,@CompanyId,@CompanyName,@Medical)

GO 
CREATE PROC DeletePoEmp(
@EmpNo varchar(10)
)
AS
DELETE policiesonemployees WHERE EmpNo=@EmpNo

GO
CREATE PROC UpdatePoEmp(
@EmpNo varchar(10),
@PolicyId int,
@PolicyName varchar(50),
@policyAmount money,
@PolicyDuration decimal(7,2),
@Emi decimal(7,2),
@PStartDate datetime,
@PendDate datetime,
@CompanyId varchar(30),
@CompanyName varchar(50),
@Medical varchar(50)
)
AS
UPDATE policiesonemployees SET
PolicyId=@PolicyId,
PolicyName =@PolicyName,
policyAmount=@policyAmount,
PolicyDuration=@PolicyDuration,
Emi=@Emi,
PStartDate=@PStartDate,
PendDate=@PendDate,
CompanyId=@CompanyId,
CompanyName=@CompanyName,
Medical=@Medical
WHERE EmpNo =@EmpNo

GO
CREATE PROC GetPoEmpByEmpNo
(
@EmpNo varchar(10)
)
AS
SELECT * FROM policiesonemployees WHERE EmpNo=@EmpNo

GO

CREATE PROC ListPolicyApproval
AS
SELECT * FROM PolicyApprovalDetails
GO

CREATE PROC InsertPolicyApproval(
@PolicyId int,
@RequestId int,
@Date datetime,
@Amount money,
@Status char(3),
@Reason varchar(50)
)
AS
INSERT INTO PolicyApprovalDetails VALUES(@PolicyId,@RequestId,@Date,@Amount,@Status,@Reason)

GO

CREATE PROC DeletePolicyApproval(
@PolicyId int
)
AS
DELETE PolicyApprovalDetails WHERE PolicyId=@PolicyId

Go
CREATE PROC UpdatePolicyApprovalInfo(
@PolicyId int,
@RequestId int,
@Date datetime,
@Amount money,
@Status char(3),
@Reason varchar(50)
)
AS
UPDATE PolicyApprovalDetails SET
RequestId =@RequestId,
[Date]=@Date,
Amount=@Amount,
[Status]= @Status,
Reason=@Reason
WHERE PolicyId=@PolicyId

GO
CREATE PROC GetPolicyApprovalById
(
@PolicyId int
)
AS
SELECT * FROM PolicyApprovalDetails WHERE PolicyId=@PolicyId

GO
CREATE PROC ListPoRe
AS
SELECT * FROM PolicyRequestDetails

GO
CREATE  PROC InsertPoRe(
@RequestDate datetime,
@EmpNo int,
@PolicyId int,
@PolicyName varchar(50),
@PolicyAmount money,
@Emi money,
@CompanyId int,
@CompanyName varchar(50),
@status varchar(50)
)
AS
INSERT INTO PolicyRequestDetails VALUES(@RequestDate,@EmpNo,@PolicyId,@PolicyName,@PolicyAmount,@Emi,@CompanyId,@CompanyName,@status)

GO

CREATE PROC DeletePoRe(
	@RequestId int
)AS
DELETE PolicyRequestDetails WHERE RequestId=@RequestId

GO

CREATE PROC UpdatePoRe(
@RequestId int,
@RequestDate datetime,
@EmpNo int,
@PolicyId int,
@PolicyName varchar(50),
@PolicyAmount money,
@Emi money,
@CompanyId int,
@CompanyName varchar(50),
@Status varchar(50)
)
AS
UPDATE PolicyRequestDetails SET
RequestDate=@RequestDate,
EmpNo=@EmpNo,
PolicyId=@PolicyId,
PolicyName=@PolicyName,
PolicyAmount=@PolicyAmount,
Emi=@Emi,
CompanyId=@CompanyId,
CompanyName=@CompanyName,
[Status]= @Status
WHERE RequestId=@RequestId

GO

CREATE PROC GetPoReByReId(
@RequestId int
)
AS
SELECT * FROM PolicyRequestDetails WHERE RequestId=@RequestId

Go

CREATE PROC ListPoToDes
AS SELECT * FROM PolicyTotalDescription

GO
CREATE PROC InsertPoToDes(
@PolicyId int,
@PolicyName varchar(50),
@PolicyDes varchar(250),
@PolicyAmount money,
@Emi money,
@PolicyDurationMonths int,
@CompanyName varchar(50),
@MedicalId varchar(50)

)
AS
INSERT INTO PolicyTotalDescription VALUES(@PolicyId,@PolicyName,@PolicyDes,@PolicyAmount,@Emi,@PolicyDurationMonths,@CompanyName,@MedicalId)

GO 
CREATE PROC DeletePoToDes(
@PolicyId int
)
AS
DELETE PolicyTotalDescription WHERE PolicyId=@PolicyId

GO
CREATE PROC UpdatePoToDes(
@PolicyId int,

@PolicyDes varchar(250),
@PolicyAmount money,
@Emi money,
@PolicyDurationMonths int,
@CompanyName varchar(50),
@MedicalId varchar(50)

)AS
UPDATE PolicyTotalDescription SET 
PolicyDes=@PolicyDes,
PolicyAmount=@PolicyAmount,
Emi=@Emi,
PolicyDurationMonths=@PolicyDurationMonths,
CompanyName=@CompanyName,
MedicalId=@MedicalId

WHERE PolicyId=@PolicyId

GO

CREATE PROC GetPoToDesByPoId
(
@PolicyId int
)
AS
SELECT * FROM PolicyTotalDescription WHERE PolicyId=@PolicyId

Go

CREATE PROC memberlogin(
@username varchar(50),
@password varchar(50)
)
AS
SELECT * FROM EmpRegister Where UserName=@username and [PassWord]=@password

GO
CREATE PROC ChangePassEmp(
@UserName varchar(50),
@PassWord varchar(50)
)
AS
UPDATE EmpRegister SET [PassWord] =@PassWord Where UserName =@UserName

GO
CREATE PROC infomember(
@username varchar(50)
)
AS
SELECT * FROM EmpRegister WHERE UserName=@username
GO

CREATE PROC listnews
AS
SELECT * FROM News

GO
Create PROC insertNews(
@title varchar(100),
@description varchar(max)
)
as
Insert into News values(@title,@description)
Go
CREATE PROC deleteNews(
@id int
)
AS DELETE News WHERE id=@id
GO
CREATE PROC GetnewsById(
	@id int
)
AS
SELECT * FROm News WHERE id = @id
Go
CREATE PROC UpdateNews(
@id int,
@title varchar(100),
@Description varchar(max)
)AS
UPDATE News SET title=@title,[description]=@Description WHERE id=@id


GO

CREATE PROC SearchByAmount(
	@minAmount money,
	@maxamount money
)
AS
SELECT * FROM Policies Where Amount BETWEEN @minAmount AND @maxamount
GO

CREATE PROCEDURE SearchCompany
    @name VARCHAR(50),
    @address varchar(150)
    
AS
SELECT P.*
FROM dbo.CompanyDetails P
WHERE (P.CompanyName = isnull(@name, '') OR P.CompanyName like '%'+ @name+ '%')
AND (P.[Address] = isnull(@address,'') OR P.[Address] like '%'+@address+'%')

GO
CREATE  PROC SearchEmployee(
	
	@name varchar(50),
	@address varchar(150),
	@designation varchar(50)
)
AS
SELECT P.*
FROM dbo.EmpRegister P

WHERE (P.LastName = isnull(@name, '') OR P.LastName like '%'+ @name+ '%')
AND (P.[Address] = isnull(@address, '') OR P.[Address] like '%'+ @address+ '%')

AND (P.Designation = isnull(@designation, '') OR P.Designation like '%'+ @designation+ '%')

Go
CREATE  PROC findNews(
@title varchar(100)
)
AS 
SELECT * FROM News WHERE title LIKE '%' + @title + '%'

GO
Create  PROC SearchpolicybyName(
@policyname varchar(50)
)
As
SELECT * FROm Policies Where PolicyName LIKE '%' + @policyname + '%'